{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%docker run -d --name mysql  -p 3307:3306 -v /data/mysql:/var/lib/mysql -v /data/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: http://mirrors.aliyun.com/pypi/simple/\n",
      "Collecting PyMySQL\n",
      "  Downloading http://mirrors.aliyun.com/pypi/packages/e5/30/20467e39523d0cfc2b6227902d3687a16364307260c75e6a1cb4422b0c62/PyMySQL-1.1.0-py3-none-any.whl (44 kB)\n",
      "     ---------------------------------------- 0.0/44.8 kB ? eta -:--:--\n",
      "     --------- ------------------------------ 10.2/44.8 kB ? eta -:--:--\n",
      "     --------- ------------------------------ 10.2/44.8 kB ? eta -:--:--\n",
      "     --------- ------------------------------ 10.2/44.8 kB ? eta -:--:--\n",
      "     ---------------------------------- --- 41.0/44.8 kB 217.9 kB/s eta 0:00:01\n",
      "     -------------------------------------- 44.8/44.8 kB 221.3 kB/s eta 0:00:00\n",
      "Installing collected packages: PyMySQL\n",
      "Successfully installed PyMySQL-1.1.0\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install PyMySQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 在mysql 终端创建 db数据库 和 users表\n",
    "```sql\n",
    "CREATE DATABASE  db ;\n",
    "CREATE TABLE `users` (\n",
    "    `id` int(11) NOT NULL AUTO_INCREMENT,\n",
    "    `email` varchar(255) COLLATE utf8_bin NOT NULL,\n",
    "    `password` varchar(255) COLLATE utf8_bin NOT NULL,\n",
    "    PRIMARY KEY (`id`)\n",
    "  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin\n",
    "AUTO_INCREMENT=1 ;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql.cursors\n",
    "\n",
    "# 连接数据库\n",
    "connection = pymysql.connect(host='localhost',\n",
    "                             user='root',\n",
    "                             password='123456',\n",
    "                             database='db',\n",
    "                             cursorclass=pymysql.cursors.DictCursor)\n",
    "with connection:\n",
    "    try: \n",
    "        with connection.cursor() as cursor: # 游标\n",
    "            # 建立一条纪录\n",
    "            sql = \"INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)\"\n",
    "            cursor.execute(sql, ('webmaster@python.org', 'very-secret'))\n",
    "\n",
    "        # connection 不能自动提交数据，必须手动提交\n",
    "        connection.commit()\n",
    "    except:\n",
    "        connection.rollback()\n",
    "    # 使用 with 无需该操作   \n",
    "    # finally: \n",
    "        # connection.close()\n",
    "\n",
    "    with connection.cursor() as cursor:\n",
    "        # 读取纪录\n",
    "        sql = \"SELECT `id`, `password` FROM `users` WHERE `email`=%s\"\n",
    "        cursor.execute(sql, ('webmaster@python.org',))\n",
    "        result = cursor.fetchone()\n",
    "        print(result) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "{'password': 'very-secret', 'id': 1}"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.11.0 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
